from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, colors
from openpyxl.chart import LineChart, Reference
from datetime import date


# 创建 Excel 文件
def new():
    wb = Workbook()
    # sh1 = wb.active                     # 激活一个 Sheet 页
    sh3 = wb.create_sheet('人员', 0)  # 0 表示 Sheet 的位置
    sh2 = wb.create_sheet('数据')
    sh2 = wb.create_sheet('信息')
    wb.save('../02.py_excelFile/openpyxl建的Excel.xlsx')


# 设置字体样式
def set_value():
    # 设置字体样式
    bold_italic_30_font = Font(name='微软雅黑', size=30, italic=True, bold=True, color=colors.BLUE)
    bold_italic_20_font = Font(name='微软雅黑', size=30, italic=True, bold=True, color='CD1076')
    wb = Workbook()
    sh1 = wb.active
    sh1['A1'] = 'Hello!'
    sh1['B2'] = 'World!'
    sh1['C3'] = 'JiaXin!'
    sh1['B2'].font = bold_italic_30_font
    sh1['C3'].font = bold_italic_20_font
    wb.save('../02.py_excelFile/openpyxl设置数据.xlsx')


# 批量填充数据
def set_value2():
    wb = Workbook()
    sh1 = wb.active
    data_list = ['Python', 'Java', 'C++']
    url_list = ['https://www.python.org/', 'https://www.java.com/zh-CN/', 'https://cplusplus.com/']
    for i, data in enumerate(data_list):
        sh1.cell(i + 1, 1).value = data
    for j, url in enumerate(url_list):
        sh1.cell(j + 1, 2).value = url

    wb.save('../02.py_excelFile/openpyxl批量填充数据.xlsx')


# 快速设置样式
def set_style2():
    wb = Workbook()
    sh1 = wb.active
    sh1.row_dimensions[1].height = 30
    sh1.column_dimensions['A'].width = 30

    data = ['Python', 'Java', 'C++']
    for i, d in enumerate(data):
        sh1.cell(i + 1, 1).value = d
        sh1.cell(i + 1, 1).alignment = Alignment(horizontal='center', vertical='center')

    wb.save('../02.py_excelFile/openpyxl修改样式.xlsx')


# 合并单元格
def set_merge():
    wb = Workbook()
    sh1 = wb.active
    sh1.merge_cells('A1:C2')  # 合并 | 单元格顺序一定是从小到达，从前到后，从上到下，
    sh1.merge_cells('d2:e5')  # 合并

    sh1['a3'] = '横向合并'  # 设置值的时候，必须是左上角的单元格
    sh1['d2'] = '多合并'

    wb.save('../02.py_excelFile/openpyxl合并单元格.xlsx')


def set_image():
    wb = Workbook()
    sh1 = wb.active

    rows = [
        ['Date', 'Batch 1', 'Batch 2', 'Batch 3'],
        [date(2020, 12, 1), 40, 30, 25],
        [date(2020, 12, 2), 40, 25, 30],
        [date(2020, 12, 3), 50, 30, 45],
        [date(2020, 12, 4), 30, 25, 40],
        [date(2020, 12, 5), 25, 35, 30],
        [date(2020, 12, 6), 20, 40, 35]
    ]

    for row in rows:
        sh1.append(row)

    # 绘画图表
    c1 = LineChart()
    c1.title = '图表名字'  # 图表名字

    c1.x_axis.title = '横坐标名称'  # 横坐标名称
    c1.y_axis.title = '纵坐标名称'  # 纵坐标名称

    # min_col：从第几列开始读数据；    min_row：从第几行开始读数据；
    # max_col：取数据到第几列；       max_row：读数据到第几行；
    data = Reference(sh1, min_col=2, min_row=1, max_col=4, max_row=7)
    c1.add_data(data, titles_from_data=True)
    sh1.add_chart(c1, 'A9')

    wb.save('../02.py_excelFile/openpyxl生成统计图.xlsx')


if __name__ == '__main__':
    # new()           # 创建 Excel 文件
    # set_value()     # 设置字体样式
    set_value2()  # 批量填充数据
    # set_style2()
    # set_merge()
    # set_image()
